package manage.commons.utils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 生成sql工具类
 * 
 * @author dongji
 * @date 2012-9-6 下午03:35:17
 */
public class DBUtils {

	private static Log log = LogFactory.getLog(DBUtils.class);
	// 分表数量
	private final static int N = 10;
	private final static String ZERO = "0";
	private final static String SEPARATOR = "_";
	private final static String EMPTY = "";
	private final static String SELECT = "select";
	private final static String FROM = "from";
	// 最大查询数量
	private final static int MAXNUM = 100000;

	/**
	 * 获取散列表
	 * 
	 * @param table
	 *            基本表名
	 * @param flag
	 *            分表依据(用户ID)
	 * @param n
	 *            表的数量
	 * @return
	 */
	public static String getHashTable(String table, long flag, int n) {
		if (flag < 0 || n <= 0) {
			return table;
		}
		long mod = flag % n;
		String tableName = table + SEPARATOR + (mod > 10 ? mod : ZERO + mod);
		return tableName;
	}

	/**
	 * 获取散列表
	 * 
	 * @param table
	 *            基本表名(用户ID)
	 * @param flag
	 *            分表依据
	 * @return
	 */
	public static String getHashTable(String table, long flag) {
		return getHashTable(table, flag, N);
	}

	/**
	 * 生成sql对应的count语句
	 * 
	 * @param sql
	 * @return
	 */
	public static String getCountSql(String sql) {
		sql = sql.replaceAll("(^\\s*\\()|(\\)\\s*$)", "")
				.replaceAll("(?i)select\\s+", "select ")
				.replaceAll("(?i)from\\s+", "from ")
				.replaceAll("(?i)order\\s+by", "order by")
				.replaceAll("(?i)limit", "limit");
		int pointer = sql.indexOf(SELECT, 0);
		// 简单语法分析栈
		LinkedList<String> stack = new LinkedList<String>();
		stack.push(SELECT);
		while (!stack.isEmpty()) {
			String first = stack.getFirst();
			int p1 = sql.indexOf(SELECT, pointer + 4);
			int p2 = sql.indexOf(FROM, pointer + 4);
			p1 = (p1 == -1) ? MAXNUM : p1;
			p2 = (p2 == -1) ? MAXNUM : p2;
			if (p1 == MAXNUM && p2 == MAXNUM)
				throw new RuntimeException("SQL语句非法...");
			if (p1 <= p2) {
				pointer = p1;
				if (first.equals(SELECT))
					stack.push(SELECT);
				else
					stack.pop();
			} else {
				pointer = p2;
				if (first.equals(FROM))
					stack.push(FROM);
				else
					stack.pop();
			}
		}
		int fromIndex = pointer;
		sql = sql.substring(fromIndex);
		int orderIndex = sql.lastIndexOf("order by");
		if (orderIndex != -1 && sql.indexOf(")", orderIndex) < 0) {
			sql = sql.substring(0, orderIndex);
		}
		int limitIndex = sql.lastIndexOf("limit");
		if (limitIndex != -1 && sql.indexOf(")", limitIndex) < 0) {
			sql = sql.substring(0, limitIndex);
		}
		return "select count(1) count " + sql;
	}

	/**
	 * 将布尔值替换成1(true)，0(false)
	 * 
	 * @param fields
	 */
	public static void format(Map<String, Object> fields) {
		for (String key : fields.keySet()) {
			if (fields.get(key) instanceof Boolean) {
				fields.put(key, (Boolean) fields.get(key) ? 1 : 0);
			}
		}
	}

	/**
	 * 将select * 通过反射类变成select a,b,c
	 * 
	 * @param sql
	 * @param clazz
	 * @return
	 */
	public static String restrictSelectSQL(String sql, Class<?> clazz) {
		return sql.replace("*", generateTargetFields(clazz));
	}

	/**
	 * 生成目标字符串,排除序列号ID
	 * 
	 * @param clazz
	 * @return
	 */
	public static String generateTargetFields(Class<?> clazz) {
		StringBuilder sb = new StringBuilder();
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			if ("serialVersionUID".equals(field.getName()))
				continue;
			sb.append(field.getName() + ", ");
		}
		sb.delete(sb.lastIndexOf(","), sb.length());

		return sb.toString();
	}

	/**
	 * 生成select 语句
	 * 
	 * @param table
	 *            表名
	 * @param conditions
	 *            where条件
	 * @param conditionsExt
	 *            其他order by ,group by 条件
	 * @return
	 */
	public static String generateSelectSQL(String table,
			Map<String, Object> conditions, String conditionsExt) {
		if (StringUtils.isBlank(table)) {
			return null;
		}
		StringBuilder sb = new StringBuilder().append("select * from ")
				.append(table).append(" where 1 = 1");
		if (conditions != null && !conditions.isEmpty()) {
			for (Iterator<String> it = conditions.keySet().iterator(); it
					.hasNext();) {
				String key = it.next();
				sb.append(" and ").append(key).append(" = ").append(":")
						.append(key);
			}
		}
		if (StringUtils.isNotBlank(conditionsExt)) {
			sb.append(" ").append(conditionsExt);
		}
		return sb.toString();
	}

	/**
	 * 生成select 语句
	 * 
	 * @param table
	 *            表名
	 * @param conditions
	 *            where条件
	 * @return
	 */
	public static String generateSelectSQL(String table,
			Map<String, Object> conditions) {
		return generateSelectSQL(table, conditions, "");
	}

	/**
	 * 生成select 语句
	 * 
	 * @param table
	 *            表名
	 * @param key
	 *            主键
	 * @return
	 */
	public static String generateSelectSQL(String table, String key) {
		Map<String, Object> conditions = new HashMap<String, Object>();
		conditions.put(key, "");
		return generateSelectSQL(table, conditions, "");
	}

	/**
	 * 生成select 语句
	 * 
	 * @param table
	 *            表名
	 * @return
	 */
	public static String generateSelectAllSQL(String table) {
		return "select * from " + table;
	}

	/**
	 * 根据对象生成完整的插入语句 insert into tableName (x,x,x) values(x,x,x);
	 * 
	 * @param <T>
	 * @param table
	 * @param t
	 * @return
	 */
	public static <T> String generateInsertSQL(String table, T t) {
		StringBuilder sb1 = new StringBuilder();
		StringBuilder sb2 = new StringBuilder();
		sb1.append("insert into ").append(table).append(" (");
		sb2.append(") values (");

		Field[] fields = t.getClass().getDeclaredFields();
		for (Field field : fields) {
			if ("serialVersionUID".equals(field.getName()))
				continue;
			sb1.append(field.getName()).append(", ");
			sb2.append(":").append(field.getName()).append(", ");
		}
		sb1.delete(sb1.lastIndexOf(","), sb1.length());
		sb2.delete(sb2.lastIndexOf(","), sb2.length());
		sb1.append(sb2).append(")");

		return sb1.toString();
	}

	/**
	 * 根据fields生成需要insert的字段 insert into tableName (a) values(1)
	 * 
	 * @param table
	 * @param fields
	 * @return
	 */
	public static String generateInsertSQL(String table,
			Map<String, Object> fields) {
		StringBuilder sb1 = new StringBuilder();
		StringBuilder sb2 = new StringBuilder();
		sb1.append("insert into ").append(table).append(" (");
		sb2.append(" ) values (");
		for (Iterator<String> it = fields.keySet().iterator(); it.hasNext();) {
			String key = (String) it.next();
			sb1.append(key);
			sb2.append(":").append(key);
			if (it.hasNext()) {
				sb1.append(", ");
				sb2.append(", ");
			}
		}
		sb1.append(sb2).append(")");
		return sb1.toString();
	}

	/**
	 * 根据对象生成完整的替换语句 reaplce into tableName (x,x,x) values(x,x,x);
	 * 
	 * @param <T>
	 * @param table
	 * @param t
	 * @return
	 */
	public static <T> String generateReplaceSQL(String table, T t) {
		StringBuilder sb1 = new StringBuilder();
		StringBuilder sb2 = new StringBuilder();
		sb1.append("replace into ").append(table).append(" (");
		sb2.append(") values (");

		Field[] fields = t.getClass().getDeclaredFields();
		for (Field field : fields) {
			if ("serialVersionUID".equals(field.getName()))
				continue;
			sb1.append(field.getName()).append(", ");
			sb2.append(":").append(field.getName()).append(", ");
		}
		sb1.delete(sb1.lastIndexOf(","), sb1.length());
		sb2.delete(sb2.lastIndexOf(","), sb2.length());
		sb1.append(sb2).append(")");
		return sb1.toString();
	}

	/**
	 * 根据fields生成需要replace的字段 replace into tableName (a) values(1)
	 * 
	 * @param table
	 * @param fields
	 * @return
	 */
	public static String generateReplaceSQL(String table,
			Map<String, Object> fields) {
		StringBuilder sb1 = new StringBuilder();
		StringBuilder sb2 = new StringBuilder();
		sb1.append("replace into ").append(table).append(" (");
		sb2.append(") values (");
		for (Iterator<String> it = fields.keySet().iterator(); it.hasNext();) {
			String key = (String) it.next();
			sb1.append(key);
			sb2.append(":").append(key);
			if (it.hasNext()) {
				sb1.append(", ");
				sb2.append(", ");
			}
		}
		sb1.append(sb2).append(")");
		return sb1.toString();
	}

	/**
	 * 根据conditions条件生成update fields语句
	 * 
	 * @param table
	 * @param fields
	 *            conditions Map<String, Object> conditions
	 * @return
	 */
	public static String generateUpdateSQL(String table,
			Map<String, Object> fields, Map<String, Object> conditions,
			String conditionsExt) {
		if (fields == null || fields.size() == 0) {
			return null;
		}
		StringBuilder sb = new StringBuilder().append("update ").append(table)
				.append(" set ");
		for (Iterator<String> it = fields.keySet().iterator(); it.hasNext();) {
			String key = it.next();
			sb.append(key).append(" = ").append(":").append(key);
			if (it.hasNext()) {
				sb.append(", ");
			}
		}
		sb.append(" where 1 = 1");
		for (Iterator<String> it = conditions.keySet().iterator(); it.hasNext();) {
			String key = it.next();
			sb.append(" and ").append(key).append(" = ").append(":")
					.append(key);
		}
		if (!StringUtils.isEmpty(conditionsExt)) {
			sb.append(" ").append(conditionsExt);
		}
		return sb.toString();
	}

	/**
	 * 根据conditions条件生成update fields语句
	 * 
	 * @param table
	 * @param fields
	 *            conditions Map<String, Object> conditions
	 * @return
	 */
	public static String generateUpdateSQL(String table,
			Map<String, Object> fields, Map<String, Object> conditions) {
		return generateUpdateSQL(table, fields, conditions, "");
	}

	/**
	 * 根据key条件生成update fields语句
	 * 
	 * @param table
	 * @param fields
	 * @param key
	 * @return
	 */
	public static String generateUpdateSQL(String table,
			Map<String, Object> fields, String key) {
		Map<String, Object> conditions = new HashMap<String, Object>();
		conditions.put(key, "");
		return generateUpdateSQL(table, fields, conditions, "");
	}

	/**
	 * 根据conditions生成delete语句可以conditionsExt limit 1
	 * 
	 * @param table
	 * @param conditions
	 * @param conditionsExt
	 * @return
	 */
	public static String generateDeleteSQL(String table,
			Map<String, Object> conditions, String conditionsExt) {
		if (conditions == null || conditions.size() == 0) {
			return null;
		}

		StringBuilder sb = new StringBuilder().append("delete from ")
				.append(table).append(" where 1 = 1");
		for (Iterator<String> it = conditions.keySet().iterator(); it.hasNext();) {
			String key = it.next();
			sb.append(" and ").append(key).append(" = ").append(":")
					.append(key);
		}

		if (!StringUtils.isEmpty(conditionsExt)) {
			sb.append(" ").append(conditionsExt);
		}

		return sb.toString();
	}

	/**
	 * 根据conditions生成delete语句
	 * 
	 * @param table
	 * @param conditions
	 * @return
	 */
	public static String generateDeleteSQL(String table,
			Map<String, Object> conditions) {
		return generateDeleteSQL(table, conditions, "");
	}

	/**
	 * 根据key生成delete语句
	 * 
	 * @param table
	 * @param conditions
	 * @return
	 */
	public static String generateDeleteSQL(String table, String key) {
		Map<String, Object> conditions = new HashMap<String, Object>();
		conditions.put(key, "");
		return generateDeleteSQL(table, conditions, "");
	}

	/**
	 * 根据conditions条件生成table的count(1) 语句
	 * 
	 * @param table
	 * @param conditions
	 * @return
	 */
	public static String generateCountSQL(String table,
			Map<String, Object> conditions) {
		StringBuilder sb = new StringBuilder().append("select count(1) from ")
				.append(table).append(" where 1 = 1");
		if (conditions != null && conditions.size() != 0) {
			for (Iterator<String> it = conditions.keySet().iterator(); it
					.hasNext();) {
				String key = it.next();
				sb.append(" and ").append(key).append(" = ").append(":")
						.append(key);
			}
		}
		sb.append(" limit 1");
		return sb.toString();
	}

	/**
	 * 生成count 语句
	 * 
	 * @param table
	 *            表名
	 * @param key
	 * 
	 * @return
	 */
	public static String generateCountSQL(String table, String key) {
		Map<String, Object> conditions = new HashMap<String, Object>();
		conditions.put(key, "");
		return generateCountSQL(table, conditions);
	}

	/**
	 * 生成count 语句
	 * 
	 * @param table
	 *            表名
	 * @return
	 */
	public static String generateCountSQL(String table) {
		return "select count(1) from " + table;
	}

	/**
	 * 得到2个相同对象之间同属性不同值的Map
	 * 
	 * @param src
	 * @param dest
	 * @return
	 */
	public static Map<String, Object> comparePojo(Object src, Object dest) {
		if (dest == null
				|| (src != null && !dest.getClass().equals(src.getClass()))) {
			return null;
		}
		Map<String, Object> values = new HashMap<String, Object>();
		Field[] fields = dest.getClass().getDeclaredFields();
		for (Field field : fields) {
			String name = field.getName();
			Object value = null;
			if ("serialVersionUID".equals(name))
				continue;
			Object v1 = null;
			Object v2 = null;
			try {
				String methodPrefix = boolean.class.equals(field.getType()) ? "is"
						: "get";
				String methodName = new StringBuilder(methodPrefix).append(
						getFirstUpperString(name)).toString();
				Class<? extends Object> c = dest.getClass();
				Method m = c.getDeclaredMethod(methodName);
				Object arg=new Object[] {};
				v1 = m.invoke(dest,arg);
				if (src != null) {
					v2 = m.invoke(src,arg);
				}
			} catch (Exception e) {
				log.error(e + " : name=" + name + ", value=" + value, e);
			}
			if (v1 != null && !v1.equals(v2)) {
				values.put(name, v1);
			}
		}
		return values;
	}

	/**
	 * 把字符串首字母大写
	 * 
	 * @param s
	 * @return
	 */
	public static String getFirstUpperString(String s) {
		if (s == null || s.length() < 1) {
			return EMPTY;
		}
		return s.substring(0, 1).toUpperCase() + s.substring(1);
	}

}
